<?php

class Mod_chart extends Model {

    function Mod_chart() {
        parent::model();
    }

    function index() {
        
    }
	function getOldestYear(){
		$data = $this->db->query('
				SELECT YEAR(sal_date) as year
				FROM tbl_sale
				INNER JOIN tbl_card ON tbl_card.car_id = tbl_sale.car_id
				JOIN tbl_car_type ON tbl_car_type.car_type_id = tbl_card.car_type_id
				WHERE car_type_name="public" ORDER BY sal_date ASC LIMIT 1'
				);
		 return $data;
	}
	function getOldestYearPr(){
		$data = $this->db->query('
				SELECT YEAR(sal_date) as year
				FROM tbl_sale
				INNER JOIN tbl_card ON tbl_card.car_id = tbl_sale.car_id
				JOIN tbl_car_type ON tbl_car_type.car_type_id = tbl_card.car_type_id
				WHERE car_type_name="private" ORDER BY sal_date ASC LIMIT 1'
				);
		 return $data;
	}
	function getOldestYearPro(){
		$data = $this->db->query('
				SELECT YEAR(sal_date) as year
				FROM tbl_sale
				INNER JOIN tbl_product ON tbl_product.pro_id = tbl_sale.pro_id
				JOIN tbl_pro_type ON tbl_pro_type.pro_type_id = tbl_product.pro_type_id
				ORDER BY sal_date ASC LIMIT 1'
				);
		 return $data;
	}
	function getPublicClass($year){
		 $data = $this->db->query('
				SELECT SUM(sal_payment)AS outcome, SUM(sal_quantity*sal_price) AS income, SUM(sal_quantity*sal_price-sal_payment) AS benifit
				FROM tbl_sale
				INNER JOIN tbl_card ON tbl_card.car_id = tbl_sale.car_id
				JOIN tbl_car_type ON tbl_car_type.car_type_id = tbl_card.car_type_id
				WHERE YEAR( tbl_sale.sal_date ) = '.$year.' and car_type_name="public"'
				);
		 return $data;
	}
	function getPrivateClass($year){
		 $data = $this->db->query('
				SELECT SUM(sal_payment)AS outcome, SUM(sal_quantity*sal_price) AS income, SUM(sal_quantity*sal_price-sal_payment) AS benifit
				FROM tbl_sale
				INNER JOIN tbl_card ON tbl_card.car_id = tbl_sale.car_id
				JOIN tbl_car_type ON tbl_car_type.car_type_id = tbl_card.car_type_id
				WHERE YEAR( sal_date ) = '.$year.'  and car_type_name="private"'
				);
		 return $data;
	}
	function getProduct($year){
		 $data = $this->db->query('
				SELECT  SUM(sal_price*sal_quantity) AS credit
				FROM tbl_sale
				INNER JOIN tbl_product ON tbl_product.pro_id = tbl_sale.pro_id
				JOIN tbl_pro_type ON tbl_pro_type.pro_type_id = tbl_product.pro_type_id
				WHERE YEAR( sal_date ) ='.$year.''
				);
		 return $data;
	}
	//=============== Monthly ======================
	function getMonthlyPub(){
		$data=$this->db->query('SELECT (sal_price*sal_quantity) AS income, (sal_payment) AS outcome, ((sal_price*sal_quantity)-sal_payment) as benifit, sal_date
				FROM tbl_sale
				INNER JOIN tbl_card ON tbl_card.car_id = tbl_sale.car_id
				JOIN tbl_car_type ON tbl_car_type.car_type_id = tbl_card.car_type_id
				WHERE month( sal_date ) = month( curdate( ) ) and car_type_name="public"');
		return $data;
	}
	
}